package com.rockcent.mall.lib.repository;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.rockcent.common.common.PageResult;
import com.rockcent.common.common.SQLObjectDoing;
import com.rockcent.common.common.SQLPageDoing;
import com.rockcent.common.domain.enumclass.Whether;
import com.rockcent.common.utils.RDateUtils;
import com.rockcent.mall.lib.domain.enumclass.MerchantOrderSourceType;
import com.rockcent.mall.lib.domain.enumclass.ProductType;
import com.rockcent.mall.lib.dto.MerchantOrderVerificationDto;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * Created by DK on 16/10/29.
 */
@Repository
public class MerchantOrderVerificationRepositoryImpl implements MerchantOrderVerificationRepositoryCustom {
    private static final Logger log = LoggerFactory.getLogger(MerchantOrderVerificationRepositoryImpl.class);

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    @Autowired
    private ObjectMapper objectMapper;

    /**
     * @param merchantId
     * @param merchantStoreId
     * @param productType
     * @param keyword
     * @param beginDate
     * @param endDate
     * @param pageResult
     * @return
     */
    @Override
    public PageResult<MerchantOrderVerificationDto> findMerchantOrderVerificationPage(Long merchantId, Long merchantStoreId, String keyword, String productType, String beginDate, String endDate, PageResult pageResult) {

        SQLPageDoing<MerchantOrderVerificationDto> doing = (sql, fields, params, typeReferenceMaP) -> {
            sql.append("select %s from merchant_order_verification mov left join product p on mov.product_id = p.id where 1=1");

            if (ProductType.ALL != ProductType.valueOf(productType)) {
                sql.append(" and p.type = :productType");
                params.put("productType", productType);
            }

            if (merchantId != null) {
                sql.append(" and mov.merchant_id = :merchantId");
                params.put("merchantId", merchantId);
            }

            if (merchantStoreId != null) {
                sql.append(" and mov.merchant_store_id = :merchantStoreId");
                params.put("merchantStoreId", merchantStoreId);
            }

            if (StringUtils.isNotBlank(keyword)) {
                sql.append(" AND (p.id =:key  OR p.name  LIKE :keyword)");
                params.put("key", keyword);
                params.put("keyword", "%" + keyword + "%");
            }

            sql.append(" AND mov.date_created < :endDate AND mov.date_created > :beginDate");
            params.put("beginDate", RDateUtils.beforeDateStr(beginDate, 0));
            params.put("endDate", RDateUtils.afterDateStr(endDate, 1));
            sql.append(" order by mov.date_created desc");
            fields.append("mov.*");
        };
        return doing.go(pageResult, MerchantOrderVerificationDto.class, objectMapper, jdbcTemplate, log);
    }

    /**
     * 订单核销数
     *
     * @param mallId
     * @param merchantId
     * @param type
     *@param typeId
     * @param orderSourceType
     * @param startDate
     * @param endDate     @return
     */
    @Override
    public Long countMerchantOrderVerificationByMallIdAndMerchantId(Long mallId, Long merchantId, String type, Long typeId, MerchantOrderSourceType orderSourceType, String startDate, String endDate) {

        /**
         * SELECT COUNT(mov.`id`) FROM merchant_order_verification mov
         LEFT JOIN merchant_order_item moi ON moi.`id`=mov.`merchant_order_item_id`
         LEFT JOIN product p ON p.`id`=moi.`product_id`
         WHERE mov.`is_delete`='NO'
         AND mov.`merchant_id`=1
         AND mov.`mall_id`=1
         AND p.`type`='GOOD'
         AND p.`market_id` IS NULL;
         */
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_verification mov");
            sql.append(" LEFT JOIN merchant_order_item moi ON moi.`id`=mov.`merchant_order_item_id`");
            sql.append(" LEFT JOIN product p ON p.`id`=moi.`product_id`");
            sql.append(" LEFT JOIN channel c ON c.`id`=moi.channel_id");
            sql.append(" WHERE mov.`is_delete`='NO'");
            sql.append(" AND mov.`merchant_id`=:merchantId");
            sql.append(" AND mov.`mall_id`=:mallId");

            params.put("merchantId", merchantId);
            params.put("mallId", mallId);

            if(type!=null && ProductType.valueOf(type)!= ProductType.ALL){
                sql.append(" AND  p.type=:type");
                params.put("type", type);
            }

            if (typeId != null) {
                sql.append(" AND  c.channel_type_id=:typeId");
                params.put("typeId", typeId);
            }

            if (orderSourceType != null && orderSourceType != MerchantOrderSourceType.ALL) {
                if (orderSourceType == MerchantOrderSourceType.MARKET) {
                    sql.append(" AND p.market_id is not null");
                }
                if (orderSourceType == MerchantOrderSourceType.SELF) {
                    sql.append(" AND p.market_id is null");
                }
            }
            if (startDate != null) {
                sql.append(" AND mov.`date_created` >=:startDate");
                params.put("startDate", startDate);
            }
            if (endDate != null) {
                sql.append(" AND mov.`date_created` <=:endDate");
                params.put("endDate", endDate);
            }
            fields.append("COUNT(mov.`id`) ");
        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }


    /**
     * 渠道核销人数
     *
     * @param channelId
     * @param mallId
     * @param type
     *@param merchantId
     * @param typeId
     * @param orderSourceType
     * @param startDate
     * @param endDate      @return
     */
    @Override
    public Long countMerchantCustomerVerificationByMallIdAndMerchantId(Long channelId, Long mallId, String type, Long merchantId, Long typeId, MerchantOrderSourceType orderSourceType, String startDate, String endDate) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_verification mov  LEFT JOIN merchant_order_item moi ON moi.`id`=mov.`merchant_order_item_id`");
            sql.append(" LEFT JOIN merchant_order mo ON mo.`id`=moi.`merchant_order_id`");
            sql.append(" LEFT JOIN product p ON p.`id`=moi.`product_id`");
            sql.append(" LEFT JOIN channel c ON c.`id`=moi.channel_id");
            sql.append(" WHERE moi.`merchant_id`=:merchantId");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`channel`=:channelId");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("channelId", channelId);

            if(type!=null && ProductType.valueOf(type)!= ProductType.ALL){
                sql.append(" AND  p.type=:type");
                params.put("type", type);
            }
            if (typeId != null) {
                sql.append(" AND  c.channel_type_id=:typeId");
                params.put("typeId", typeId);
            }


            if (orderSourceType != null && orderSourceType != MerchantOrderSourceType.ALL) {
                if (orderSourceType == MerchantOrderSourceType.SELF) {
                    sql.append(" AND p.market_id is null");
                }
                if (orderSourceType == MerchantOrderSourceType.MARKET) {
                    sql.append(" AND p.market_id is not null");
                }
            }
            if (startDate != null) {
                sql.append(" AND mov.`date_created` >=:startDate");
                params.put("startDate", startDate);
            }
            if (endDate != null) {
                sql.append(" AND mov.`date_created` <=:endDate");
                params.put("endDate", endDate);
            }
            fields.append("CASE WHEN  COUNT( distinct mo.`customer_id`) IS NULL THEN 0 ELSE  COUNT( distinct mo.`customer_id`)  END");
        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 昨日核销数
     *
     * @param mallId
     * @param merchantId
     * @return
     */
    @Override
    public Long getVerifyOrderCountStatisticsYesterDay(Long mallId, Long merchantId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            /**
             * select count( distinct mo.id) from merchant_order_verification mov
             left join merchant_order_item moi on moi.id=mov.merchant_order_item_id
             left join merchant_order mo on mo.id=moi.merchant_order_id
             */
            sql.append("select %s from merchant_order_item moi");
            sql.append(" left join merchant_order mo on mo.id=moi.merchant_order_id");
            sql.append(" where moi.is_delete='NO'");
            sql.append(" and moi.merchant_id=:merchantId");
            sql.append(" and moi.mall_id=:mallId");
            sql.append(" and DATE(moi.date_created)=CURDATE()-1");
            sql.append(" and moi.verify_count > 0");
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);

            fields.append("count(1)");


        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * @param mallId
     * @param merchantId
     * @return
     */
    @Override
    public Long getVerifyCustomerCountStatisticsYesterDay(Long mallId, Long merchantId) {

        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_item moi");
            sql.append(" left join merchant_order mo on mo.id=moi.merchant_order_id");
            sql.append(" WHERE moi.`is_delete`='NO'");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND DATE(moi.date_created)=CURDATE()-1");
            sql.append(" and moi.verify_count > 0");
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);
            fields.append("count(distinct mo.`customer_id`)");
        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * @param mallId
     * @param merchantId
     * @return
     */
    @Override
    public Long getVerifyProductCountStatisticsYesterDay(Long mallId, Long merchantId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_item moi");
            sql.append(" left join merchant_order mo on mo.id=moi.merchant_order_id");
            sql.append(" WHERE moi.`is_delete`='NO'");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" and DATE(moi.date_created)=CURDATE()-1");
            sql.append(" and moi.verify_count > 0");
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);
            fields.append("sum(moi.verify_count)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     *
     * @param mallId
     * @param merchantId
     * @return
     */
    @Override
    public Double getVerifyOrderTotalStatisticsYesterDay(Long mallId, Long merchantId) {
        SQLObjectDoing<Double> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_item moi");
            sql.append(" left join merchant_order mo on mo.id=moi.merchant_order_id");
            sql.append(" WHERE moi.`is_delete`='NO'");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" and DATE(moi.date_created)=CURDATE()-1");
            sql.append(" and moi.verify_count > 0");
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);
            fields.append("sum(moi.discount_amount/moi.buy_count*moi.verify_count)");
        };
        return doing.go(Double.class, jdbcTemplate, log, objectMapper);
    }
}
